﻿using Dapper;
using System.Data;
using System.Data.SQLite;


namespace ClassLibrary_fqy_NET.SQLiteHelper
{
    /// <summary>
    /// SQLite(Dapper)操作类
    /// </summary>
    public class SystemSQLiteHelper
    {
        private SQLiteConnection? _connection;


        /// <summary>
        /// 初始化，高性能并发操作
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public SystemSQLiteHelper(string connectionString)
        {
            var strConnect = new SQLiteConnectionStringBuilder()
            {
                DataSource = connectionString,
                JournalMode = SQLiteJournalModeEnum.Wal
            }.ToString();
            _connection = new SQLiteConnection(strConnect);
            _connection.Open();
        }
        /// <summary>
        /// 插入数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> InsertDataTransactionAsync<T>(string sql, T t) where T : class
        {
            int result = 0;
            if (_connection != null)
            {
                using (var transction = _connection.BeginTransaction())
                {
                    try
                    {
                        result = await _connection.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 批量插入数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> InsertDataTransactionAsync<T>(string sql, List<T> t) where T : class
        {
            int result = 0;
            if (_connection != null)
            {
                using (var transction = _connection.BeginTransaction())
                {
                    try
                    {
                        result = await _connection.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <returns></returns>
        public static async Task<List<T>> QueryDataAsync<T>(string connString, string sql) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.QueryAsync<T>(sql);
                return result.ToList();
            }
        }
        /// <summary>
        /// 查询数据(按条件查询)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns></returns>
        public static async Task<List<T>> QueryDataAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.QueryAsync<T>(sql, t);
                return result.ToList();
            }
        }
        /// <summary>
        /// 查询数据(按条件查询)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public static async Task<List<T>> QueryDataAsync<T>(string connString, string sql, object param) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.QueryAsync<T>(sql, param);
                return result.ToList();
            }
        }
        /// <summary>
        /// 查询数据并返回DataTable
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="param">查询参数</param>
        /// <returns></returns>
        public static async Task<DataTable> QueryDataReaderAsync(string connString, string sql, object param)
        {
            using (var conn = new SQLiteConnection(connString))
            {
                DataTable dt = new DataTable();
                var reader = await conn.ExecuteReaderAsync(sql, param);
                dt.Load(reader);
                return dt;
            }
        }
        /// <summary>
        /// 插入数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> InsertDataTransactionAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                int result = 0;
                await conn.OpenAsync();//使用事务需要手动打开数据库连接
                using (var transction = conn.BeginTransaction())
                {
                    try
                    {
                        result = await conn.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
                return result;
            }
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> InsertDataAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }
        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入泛型类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> InsertDataAsync<T>(string connString, string sql, List<T> t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }
        /// <summary>
        /// 删除数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> DeleteDataTransactionAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                int result = 0;
                await conn.OpenAsync();//使用事务需要手动打开数据库连接
                using (var transction = conn.BeginTransaction())
                {
                    try
                    {
                        result = await conn.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
                return result;
            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> DeleteDataAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入泛型类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> DeleteDataAsync<T>(string connString, string sql, List<T> t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }
        /// <summary>
        /// 更新数据(带事务)
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> UpdateDataTransactionAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                int result = 0;
                await conn.OpenAsync();//使用事务需要手动打开数据库连接
                using (var transction = conn.BeginTransaction())
                {
                    try
                    {
                        result = await conn.ExecuteAsync(sql, t, transction);
                        transction.Commit();//提交事务
                    }
                    catch
                    {
                        transction.Rollback();//出现异常，回滚事务
                        result = -1;
                    }
                }
                return result;
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入实体类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> UpdateDataAsync<T>(string connString, string sql, T t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }
        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="sql">sql执行语句</param>
        /// <param name="t">传入泛型类</param>
        /// <returns>受影响的行数</returns>
        public static async Task<int> UpdateDataAsync<T>(string connString, string sql, List<T> t) where T : class
        {
            using (var conn = new SQLiteConnection(connString))
            {
                var result = await conn.ExecuteAsync(sql, t);
                return result;
            }
        }

    }
}
